﻿using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;

namespace ChinaDynastyTours.Controls
{
    class ServiceInfoCtrl
    {
        #region 获取地接社数据  

        internal static DataTable GetTravelAgentServiceData()
        {

            Database db = DatabaseFactory.CreateDatabase();

            string sql = "SELECT [TravelAgentID], [City], [ProviderID], [ProviderName], [ServiceName], [Price], [Remark] FROM srv_TravelAgent";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        
        /// <summary>
        /// 获取旅行社服务数据
        /// </summary>
        /// <param name="providerId">供应商代码</param>
        /// <returns></returns>
        internal static DataTable GetTravelAgentServiceData(string providerId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT [TravelAgentID], [City], [ProviderID], [ProviderName], [ServiceName], [Price], [Remark] FROM srv_TravelAgent WHERE ProviderId={0}", providerId);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region 获取景点门票服务信息

        /// <summary>
        /// 获取全部景点门票的服务信息
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetParkTicketServiceData(int isSigned)
        {

            Database db = DatabaseFactory.CreateDatabase();
            string sql;

            if (isSigned == -1)
            {
                sql = @"SELECT [ParkTicketID], [City], [ProviderID], [ProviderName], [ServiceName], [OffSeasonPrice], [BusySeasonPrice], [ContractPath] FROM srv_ParkTicket";
            }
            else
            {
                sql = string.Format(@"SELECT [ParkTicketID], srv_ParkTicket.[City], srv_ParkTicket.[ProviderID], srv_ParkTicket.[ProviderName], [ServiceName], [OffSeasonPrice], [BusySeasonPrice], [ContractPath] 
                        FROM srv_ParkTicket,bas_Provider WHERE srv_ParkTicket.[ProviderID] = bas_Provider.[ProviderID] and bas_Provider.IsSigned ='{0}'", isSigned);
            }
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        
         /// <summary>
        /// 获取全部演出门票的服务信息
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetPlayTicketServiceData(int isSigned)
        {

            Database db = DatabaseFactory.CreateDatabase();
            string sql;

            if (isSigned == -1)
            {
                sql = @"SELECT [PlayTicketID], [City], [ProviderID], [ProviderName], [ServiceName], [Price], [ContractPath] FROM srv_PlayTicket";
            }
            else
            {
                sql = string.Format(@"SELECT [PlayTicketID], srv_PlayTicket.[City], srv_PlayTicket.[ProviderID], srv_PlayTicket.[ProviderName], [ServiceName], [Price], [ContractPath] 
                        FROM srv_PlayTicket,bas_Provider WHERE srv_PlayTicket.[ProviderID] = bas_Provider.[ProviderID] and bas_Provider.IsSigned ='{0}'", isSigned);
            }
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取全部餐厅的服务信息
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetRestaurantServiceData(int isSigned)
        {

            Database db = DatabaseFactory.CreateDatabase();
            string sql;

            if (isSigned == -1)
            {
                sql = @"SELECT [RestaurantID], [City], [ProviderID], [ProviderName],[ServiceName],[Price1],[Price2],[Price3],[Price4],[Price5],[Price6],[Price7],[Price8],[Price9],[Price10], [AccompanyUnitPrice], [ContractPath] 
                        FROM srv_Restaurant";
            }
            else
            {
                sql = string.Format(@"SELECT [RestaurantID], srv_Restaurant.[City], srv_Restaurant.[ProviderID], srv_Restaurant.[ProviderName],[ServiceName],[Price1],[Price2],[Price3],[Price4],[Price5],[Price6],[Price7],[Price8],[Price9],[Price10], [AccompanyUnitPrice], [ContractPath] 
                        FROM srv_Restaurant,bas_Provider WHERE srv_Restaurant.[ProviderID] = bas_Provider.[ProviderID] and bas_Provider.IsSigned ='{0}'", isSigned);
            }

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取全部汽车的服务信息
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetVhicleServiceData()
        {

            Database db = DatabaseFactory.CreateDatabase();

            string sql = @"SELECT [VehicleID], [City], [ProviderID],[VehicleType], [ProviderName], [ServiceName], [Price], [ContractPath] FROM srv_Vehicle";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }      

        #endregion


        #region 获取宾馆数据

        internal static DataTable GetHotelServiceData()
        {

            Database db = DatabaseFactory.CreateDatabase();


            string sql = "SELECT [HotelID], [City], [ProviderID], [ProviderName], [FeaturesRoom], [ContractPath] FROM srv_Hotel";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        
        /// <summary>
        /// 获取宾馆服务数据
        /// </summary>
        /// <param name="providerId">供应商代码</param>
        /// <returns></returns>
        internal static DataTable GetHotelServiceData(string providerId)
        {

            Database db = DatabaseFactory.CreateDatabase();


            string sql = string.Format(@"SELECT [HotelID], [City], [ProviderID], [ProviderName], [FeaturesRoom], [ContractPath] FROM srv_Hotel WHERE ProviderID={0}", providerId);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion
    }
}
